package com.party.web.biz.competition;

import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.party.common.constant.Constant;
import com.party.common.utils.BigDecimalUtils;
import com.party.common.utils.DateUtils;
import com.party.common.utils.Encodes;
import com.party.common.utils.StringUtils;
import com.party.core.exception.BusinessException;
import com.party.core.model.YesNoStatus;
import com.party.core.model.competition.*;
import com.party.core.model.member.Member;
import com.party.core.service.competition.*;
import com.party.core.service.member.IMemberService;
import com.party.core.service.picCloud.CosCloudBizService;
import com.party.file.cos.dto.UploadResult;
import com.party.file.cos.meta.InsertOnly;
import com.party.web.redisPubSub.publish.RedisMsgPublish;
import com.party.web.redisPubSub.subscribe.SubScribeMessage;
import com.party.web.utils.RealmUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

/**
 * 项目业务处理
 * Created by yifeng
 *
 * @date 2018/5/15 0015
 * @time 下午 16:52
 */
@Service
public class CompProjectBizService {
    @Autowired
    private ICompetitionScheduleService competitionScheduleService;
    @Autowired
    private ICompetitionMemberService competitionMemberService;
    @Autowired
    private ICompetitionGroupService competitionGroupService;
    @Autowired
    private IMemberService memberService;
    @Autowired
    private ICompetitionResultService competitionResultService;
    @Autowired
    private ICompetitionGroupResultService competitionGroupResultService;
    @Autowired
    private CosCloudBizService cosCloudBizService;
    @Autowired
    private RedisMsgPublish redisMsgPublish;
    @Autowired
    private ICompetitionProjectService competitionProjectService;

    private final static String excel2003L = ".xls";    //2003- 版本的excel
    private final static String excel2007U = ".xlsx";   //2007+ 版本的excel
    private final static String SPLIT = "-";
    private final static String RESULT = "result";
    private final static String DISTANCE = "distance";
    private final static String DATE_REG = "\\d{4}(\\-|\\/|.)\\d{1,2}\\1\\d{1,2}"; // 日期正则表达式
    private static String[] cMemberTitles = new String[]{"姓名", "队伍名", "号码牌", "手机号码"};
    private static String[] cGroupTitles = new String[]{"队伍名"};
    private static Map<Short, String> colorMap = Maps.newHashMap();

    static {
        colorMap.put(HSSFColor.ROSE.index, "用户不存在");
        colorMap.put(HSSFColor.LIGHT_GREEN.index, "用户不在该项目中");
        colorMap.put(HSSFColor.PALE_BLUE.index, "日程不存在");
        colorMap.put(HSSFColor.LAVENDER.index, "用时成绩格式不对");
        colorMap.put(HSSFColor.CORAL.index, "导入异常");
    }

    Logger logger = LoggerFactory.getLogger(getClass());

    /**
     * 生成导入模板
     *
     * @param type      类型 1：人员 2：团队
     * @param projectId 项目id
     */
    public void createTemplate(Integer type, String projectId, HttpServletResponse response) throws IOException {
        List<CompetitionSchedule> schedules = competitionScheduleService.findByProject(projectId);
        List<WithMember> members = Lists.newArrayList();
        List<CompetitionGroup> groups = Lists.newArrayList();

        CompetitionProject project = competitionProjectService.get(projectId);

        String fileName = "‘" + project.getTitle() + "’参赛人员成绩导入模板";
        if (type == 2) {
            fileName = "‘" + project.getTitle() + "’参赛团队成绩导入模板";
            groups = competitionGroupService.list(new CompetitionGroup(projectId));
        } else if (type == 1) {
            Map<String, Object> params = Maps.newHashMap();
            params.put("projectId", projectId);
            members = competitionMemberService.withMemberList(params, null);
        }

        Workbook wb = new SXSSFWorkbook();
        Sheet sheet0 = wb.createSheet("导入数据模板");
        createTitleRow(schedules, type, sheet0, 0, 1, wb);

        CellStyle cellStyle = wb.createCellStyle();
        DataFormat dataFormat = wb.createDataFormat();
        cellStyle.setDataFormat(dataFormat.getFormat("@"));

        // 写入人员信息
        for (int i = 0; i < members.size(); i++) {
            WithMember withMember = members.get(i);
            Row row = sheet0.createRow(i + 2);

            Cell name = row.createCell(0);
            String realname = StringUtils.isEmpty(withMember.getFullname()) ? withMember.getRealname() : withMember.getFullname();
            name.setCellValue(realname);

            Cell group = row.createCell(1);
            group.setCellValue(withMember.getGroupName());

            Cell number = row.createCell(2);
            number.setCellValue(withMember.getNumber());
            number.setCellStyle(cellStyle);

            Cell phone = row.createCell(3);
            phone.setCellValue(withMember.getMobile());

            int startLength = 3;
            for (int kk = 0; kk < schedules.size(); kk++) {
                Cell cellResult = row.createCell(startLength + kk + 1);
                cellResult.setCellValue("");
                cellResult.setCellStyle(cellStyle);
            }

            int colNo = startLength + schedules.size() + 1;
            Cell cellSplit = row.createCell(colNo);
            cellSplit.setCellValue("");
            cellSplit.setCellStyle(cellStyle);

            for (int kk = 0; kk < schedules.size(); kk++) {
                Cell cellResult = row.createCell(colNo + kk + 1);
                cellResult.setCellValue("");
                cellResult.setCellStyle(cellStyle);
            }
        }
        // 写入团队信息
        for (int i = 0; i < groups.size(); i++) {
            CompetitionGroup group = groups.get(i);

            Row row = sheet0.createRow(i + 2);
            Cell name = row.createCell(0);
            name.setCellValue(group.getGroupName());

            int startLength = 0;
            for (int kk = 0; kk < schedules.size(); kk++) {
                Cell cellResult = row.createCell(startLength + kk + 1);
                cellResult.setCellValue("");
                cellResult.setCellStyle(cellStyle);
            }

            int colNo = startLength + schedules.size() + 1;
            Cell cellSplit = row.createCell(colNo);
            cellSplit.setCellValue("");
            cellSplit.setCellStyle(cellStyle);

            for (int kk = 0; kk < schedules.size(); kk++) {
                Cell cellResult = row.createCell(colNo + kk + 1);
                cellResult.setCellValue("");
                cellResult.setCellStyle(cellStyle);
            }
        }

        response.reset();
        response.setContentType("application/octet-stream; charset=utf-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + Encodes.urlEncode(fileName + ".xlsx"));
        wb.write(response.getOutputStream());
    }

    /**
     * 导入人员成绩
     *
     * @param wb        文件
     * @param projectId 项目id
     * @throws Exception
     */
    public void personResultImport(Workbook wb, String projectId, String sessionId) {
        try {
            List<Map<String, String>> errorInfo = new LinkedList<Map<String, String>>();
            String currentId = RealmUtils.getCurrentUser().getId();
            int successNum = 0;
            int currentNum = 0;
            List<List<String>> sheetInfo = this.getSheetInfo(wb);
            for (int k = 0; k < sheetInfo.size(); k++) {
                currentNum++;
                List<String> rowData = sheetInfo.get(k);
                String name = rowData.get(0); // 人名
                String groupName = rowData.get(1); // 队伍名
                String number = rowData.get(2); // 号码牌
                String mobile = rowData.get(3); // 手机号
                Map<String, String> resultMap = Maps.newHashMap();
                Map<String, String> distanceMap = Maps.newHashMap();
                try {
                    resolveData(resultMap, distanceMap, rowData, 4);

                    List<String> memberIds = null;
                    if (StringUtils.isNotEmpty(mobile)) {
                        memberIds = memberService.findByMobileOrRealName(mobile, null, Member.MEMBER_PERSONAL);
                    }
                    if ((memberIds == null || memberIds.size() == 0) && StringUtils.isNotEmpty(name)) {
                        memberIds = memberService.findByMobileOrRealName(null, name, Member.MEMBER_PERSONAL);
                    }

                    if (memberIds == null || memberIds.size() == 0) {
                        throw new BusinessException(100, "用户不存在");
                    }

                    List<CompetitionMember> competitionMembers = Lists.newArrayList();
                    for (String memberId : memberIds) {
                        CompetitionMember competitionMember = competitionMemberService.findByProjectAndMember(memberId, projectId);
                        if (competitionMember != null) {
                            competitionMembers.add(competitionMember);
                        }
                    }

                    if (competitionMembers.size() == 0) {
                        throw new BusinessException(110, "用户不在该项目中");
                    }

                    int tempSuccessNum = 0;
                    for (CompetitionMember competitionMember : competitionMembers) {
                        if (StringUtils.isNotEmpty(number)) {
                            competitionMember.setNumber(number);
                            competitionMemberService.update(competitionMember);
                        }

                        int scheduleSuccess = insertPersonResult(projectId, competitionMember, resultMap, distanceMap);
                        if (scheduleSuccess == resultMap.size()) {
                            tempSuccessNum++;
                        }
                    }
                    if (tempSuccessNum > 1) {
                        tempSuccessNum = 1;
                    }
                    if (tempSuccessNum == 1) {
                        successNum++;
                    }
                } catch (BusinessException e) {
                    putErrorData(e.getCode(), name, groupName, number, mobile, resultMap, distanceMap, errorInfo);
                    continue;
                } catch (Exception e) {
                    logger.error("导入人员成绩异常", e);
                    putErrorData(9999, name, groupName, number, mobile, resultMap, distanceMap, errorInfo);
                    continue;
                } finally {
                    Map<String, Object> messageMap = Maps.newHashMap();
                    messageMap.put("totalNum", sheetInfo.size());
                    messageMap.put("currentNum", currentNum);
                    messageMap.put("isComplete", false);

                    Map<String, Object> params = Maps.newHashMap();
                    params.put("message", JSONObject.toJSONString(messageMap));
                    params.put("sessionId", sessionId);
                    params.put("memberId", currentId);
                    redisMsgPublish.publish(SubScribeMessage.COMPETITION_IMPORT_CHANNEL_NAME, JSONObject.toJSONString(params));
                }
            }
            if (currentNum == sheetInfo.size()) {
                Map<String, Object> messageMap = Maps.newHashMap();
                if (errorInfo.size() > 0) {
                    String resourceUrl = exportErrorInfo(errorInfo, projectId, 1);
                    messageMap.put("resourceUrl", resourceUrl);
                }
                messageMap.put("isComplete", true);
                messageMap.put("totalNum", sheetInfo.size());
                messageMap.put("currentNum", currentNum);
                messageMap.put("successNum", successNum);
                messageMap.put("errorNum", errorInfo.size());

                Map<String, Object> params = Maps.newHashMap();
                params.put("message", JSONObject.toJSONString(messageMap));
                params.put("sessionId", sessionId);
                params.put("memberId", currentId);
                redisMsgPublish.publish(SubScribeMessage.COMPETITION_IMPORT_CHANNEL_NAME, JSONObject.toJSONString(params));
            }
        } catch (Exception e) {
            logger.error("最外层导入人员成绩异常", e);
        }
    }

    /**
     * 导入人员成绩
     *
     * @param wb        文件
     * @param projectId 项目id
     * @param sessionId sessionId
     * @return
     * @throws Exception
     */
    public void groupResultImport(Workbook wb, String projectId, String sessionId) {
        try {
            List<Map<String, String>> errorInfo = new LinkedList<Map<String, String>>();
            String currentId = RealmUtils.getCurrentUser().getId();
            int successNum = 0;
            int currentNum = 0;
            List<List<String>> sheetInfo = this.getSheetInfo(wb);
            for (int k = 0; k < sheetInfo.size(); k++) {
                currentNum++;
                List<String> rowData = sheetInfo.get(k);
                String name = rowData.get(0); // 队伍名
                Map<String, String> resultMap = Maps.newHashMap();
                Map<String, String> distanceMap = Maps.newHashMap();
                try {
                    resolveData(resultMap, distanceMap, rowData, 1);

                    String groupId = null;
                    if (StringUtils.isNotEmpty(name)) {
                        CompetitionGroup query = new CompetitionGroup();
                        query.setProjectId(projectId);
                        query.setGroupName(name);
                        CompetitionGroup competitionGroup = competitionGroupService.getUnique(query);
                        if (competitionGroup != null) {
                            groupId = competitionGroup.getId();
                        }
                    }

                    if (StringUtils.isEmpty(groupId)) {
                        throw new BusinessException(100, "队伍不存在");
                    }
                    int scheduleSuccess = insertGroupResult(projectId, groupId, resultMap, distanceMap);
                    if (scheduleSuccess == resultMap.size()) {
                        successNum++;
                    }
                } catch (BusinessException e) {
                    putErrorData(e.getCode(), name, "", "", "", resultMap, distanceMap, errorInfo);
                    continue;
                } catch (Exception e) {
                    logger.error("导入小组成绩异常", e);
                    putErrorData(9999, name, "", "", "", resultMap, distanceMap, errorInfo);
                    continue;
                } finally {
                    Map<String, Object> messageMap = Maps.newHashMap();
                    messageMap.put("totalNum", sheetInfo.size());
                    messageMap.put("currentNum", currentNum);
                    messageMap.put("isComplete", false);

                    Map<String, Object> params = Maps.newHashMap();
                    params.put("message", JSONObject.toJSONString(messageMap));
                    params.put("sessionId", sessionId);
                    params.put("memberId", currentId);
                    redisMsgPublish.publish(SubScribeMessage.COMPETITION_IMPORT_CHANNEL_NAME, JSONObject.toJSONString(params));
                }
            }
            if (currentNum == sheetInfo.size()) {
                Map<String, Object> messageMap = Maps.newHashMap();
                if (errorInfo.size() > 0) {
                    String resourceUrl = exportErrorInfo(errorInfo, projectId, 2);
                    messageMap.put("resourceUrl", resourceUrl);
                }
                messageMap.put("isComplete", true);
                messageMap.put("totalNum", sheetInfo.size());
                messageMap.put("currentNum", currentNum);
                messageMap.put("successNum", successNum);
                messageMap.put("errorNum", errorInfo.size());

                Map<String, Object> params = Maps.newHashMap();
                params.put("message", JSONObject.toJSONString(messageMap));
                params.put("sessionId", sessionId);
                params.put("memberId", currentId);
                redisMsgPublish.publish(SubScribeMessage.COMPETITION_IMPORT_CHANNEL_NAME, JSONObject.toJSONString(params));
            }
        } catch (Exception e) {
            logger.error("最外层导入团队成绩异常", e);
        }
    }

    /**
     * 插入错误数据
     *
     * @param errorCode   错误代码
     * @param name        名称
     * @param groupName   小组名称
     * @param number      号码牌
     * @param mobile      手机号
     * @param resultMap   用时集合
     * @param distanceMap 里程集合
     * @param errorInfo   错误错误集合
     */
    private void putErrorData(Integer errorCode, String name, String groupName, String number, String mobile,
                              Map<String, String> resultMap, Map<String, String> distanceMap, List<Map<String, String>> errorInfo) {
        boolean flag = true;
        for (Map<String, String> map : errorInfo) {
            if (map.get("name").equals(name)) {
                flag = false;
            }
        }
        if (flag) {
            Map<String, String> resultMM = Maps.newHashMap();
            for (Map.Entry<String, String> mm : resultMap.entrySet()) {
                resultMM.put(RESULT + "_" + mm.getKey(), mm.getValue());
            }
            Map<String, String> distanceMM = Maps.newHashMap();
            for (Map.Entry<String, String> mm : distanceMap.entrySet()) {
                distanceMM.put(DISTANCE + "_" + mm.getKey(), mm.getValue());
            }

            Map<String, String> errorMap = new LinkedHashMap<String, String>();
            errorMap.put("errorCode", errorCode.toString());
            errorMap.put("name", name);
            errorMap.put("groupName", groupName);
            errorMap.put("number", number);
            errorMap.put("mobile", mobile);
            errorMap.putAll(resultMM);
            errorMap.put("-", "");
            errorMap.putAll(distanceMM);
            errorInfo.add(errorMap);
        }
    }

    /**
     * 解析用时和里程
     *
     * @param resultMap   用时
     * @param distanceMap 里程
     * @param rowData     行数据
     * @param colNum      开始列
     */
    private void resolveData(Map<String, String> resultMap, Map<String, String> distanceMap, List<String> rowData, int colNum) {
        for (int i = colNum; i < rowData.size(); i++) {
            String colValue = rowData.get(i);
            if (!colValue.equals(SPLIT)) {
                String[] datas = colValue.split("_");
                if (colValue.contains(RESULT)) {
                    resultMap.put(datas[1], datas.length == 3 ? datas[2] : "");
                } else if (colValue.contains(DISTANCE)) {
                    distanceMap.put(datas[1], datas.length == 3 ? datas[2] : "");
                }
            }
        }
    }

    /**
     * 获取表格信息
     *
     * @param workbook 文件
     * @return
     * @throws Exception
     */
    private List<List<String>> getSheetInfo(Workbook workbook) throws Exception {
        Pattern r = Pattern.compile(DATE_REG);
        List<List<String>> rowdatas = Lists.newArrayList();
        Sheet sheet = workbook.getSheetAt(0);
        Row titleRow = sheet.getRow(1);
        //遍历当前sheet中的所有行
        for (int j = 1; j < sheet.getLastRowNum(); j++) {
            Row row = sheet.getRow(j + 1);
            if (row == null || row.getFirstCellNum() == j) {
                continue;
            }

            int splitIndex = 0;
            //遍历所有的列
            List<String> coldatas = Lists.newArrayList();
            for (int y = row.getFirstCellNum(); y < titleRow.getLastCellNum(); y++) {
                Cell titleCell = titleRow.getCell(y);
                String cellValue = getCellValue(titleCell);
                if (cellValue.equals(SPLIT)) { // 分隔符
                    coldatas.add(SPLIT);
                    splitIndex = y;
                } else {
                    boolean matches = r.matcher(cellValue).matches();
                    Cell cell = row.getCell(y);

                    if (cell != null) {
                        String colValue = getCellValue(cell);
                        if (matches) {
                            if (splitIndex == 0) {
                                coldatas.add(RESULT + "_" + cellValue + "_" + colValue);
                            } else {
                                coldatas.add(DISTANCE + "_" + cellValue + "_" + colValue);
                            }
                        } else {
                            coldatas.add(colValue);
                        }
                    } else {
                        if (matches) {
                            if (splitIndex == 0) {
                                coldatas.add(RESULT + "_" + cellValue + "_" + "");
                            } else {
                                coldatas.add(DISTANCE + "_" + cellValue + "_" + "");
                            }
                        } else {
                            coldatas.add("");
                        }
                    }
                }
            }
            rowdatas.add(coldatas);
        }
        System.out.println(JSONObject.toJSONString(rowdatas));
        return rowdatas;
    }

    /**
     * 描述：根据文件后缀，自适应上传文件的版本
     *
     * @param inStr,fileName
     * @return
     * @throws Exception
     */
    public Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
        Workbook wb;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (excel2003L.equals(fileType)) {
            wb = new HSSFWorkbook(inStr);  //2003-
        } else if (excel2007U.equals(fileType)) {
            wb = new XSSFWorkbook(inStr);  //2007+
        } else {
            throw new Exception("解析的文件格式有误！");
        }
        return wb;
    }

    /**
     * 描述：对表格中数值进行格式化
     *
     * @param cell
     * @return
     */
    @SuppressWarnings("JavaDoc")
    private String getCellValue(Cell cell) {
        String value = null;
        DecimalFormat df = new DecimalFormat("0");  //格式化number String字符
        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化
        DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字

        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                value = cell.getRichStringCellValue().getString();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                    value = df.format(cell.getNumericCellValue());
                } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
                    value = sdf.format(cell.getDateCellValue());
                } else {
                    value = df2.format(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_BLANK:
                value = "";
                break;
            default:
                break;
        }
        return value;
    }

    /**
     * 导出错误信息
     *
     * @param errorInfo 错误信息
     * @param projectId 项目id
     * @param type      类型 1：人员 2：团队
     * @return
     */
    public String exportErrorInfo(List<Map<String, String>> errorInfo, String projectId, int type) {
        List<CompetitionSchedule> schedules = competitionScheduleService.findByProject(projectId);
        String fileName = "参赛人员成绩导入错误数据";
        if (type == 2) {
            fileName = "参赛团队成绩导入错误数据";
        }

        Workbook wb = new XSSFWorkbook();
        Sheet sheet0 = wb.createSheet("错误信息");
        Sheet sheetRemarks = wb.createSheet("错误信息颜色备注");

        int kkk = 0;
        for (Map.Entry<Short, String> entry : colorMap.entrySet()) {
            Row remarksRow0 = sheetRemarks.createRow(kkk);
            Cell colorCell = remarksRow0.createCell(0);
            CellStyle style = wb.createCellStyle();
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//设置前景填充样式
            style.setFillForegroundColor(entry.getKey());
            colorCell.setCellStyle(style);

            Cell remarksRow0Cell = remarksRow0.createCell(1);
            remarksRow0Cell.setCellValue(entry.getValue());
            kkk++;
        }

        createTitleRow(schedules, type, sheet0, 0, 1, wb);

        for (int i = 0; i < errorInfo.size(); i++) {
            Map<String, String> errorMap = errorInfo.get(i);
            CellStyle style = wb.createCellStyle();
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//设置前景填充样式
            Integer errorCode = Integer.valueOf(errorMap.get("errorCode"));
            switch (errorCode) {
                case 100: // 用户不存在
                    style.setFillForegroundColor(HSSFColor.ROSE.index);
                    break;
                case 110: // 用户不在该项目中
                    style.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
                    break;
                case 120: // 日程不存在
                    style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
                    break;
                case 130: // 用时成绩格式不对
                    style.setFillForegroundColor(HSSFColor.LAVENDER.index);
                    break;
                case 9999: // 报错
                    style.setFillForegroundColor(HSSFColor.CORAL.index);
                    break;
            }

            Row row = sheet0.createRow(2 + i);

            String name = errorMap.get("name");
            Cell cellName = row.createCell(0);
            cellName.setCellValue(name);
            cellName.setCellStyle(style);

            int startLength = 0;
            if (type == 1) {
                startLength = cMemberTitles.length - 1;

                String groupName = errorMap.get("groupName");
                Cell cellGroupName = row.createCell(1);
                cellGroupName.setCellValue(groupName);
                cellGroupName.setCellStyle(style);

                String number = errorMap.get("number");
                Cell cellNumber = row.createCell(2);
                cellNumber.setCellValue(number);
                cellNumber.setCellStyle(style);

                String mobile = errorMap.get("mobile");
                Cell cellMobile = row.createCell(3);
                cellMobile.setCellValue(mobile);
                cellMobile.setCellStyle(style);
            } else {
                startLength = cGroupTitles.length - 1;
            }


            appendScheduleColnum(errorInfo, schedules, i, style, row, startLength, RESULT);

            int col = startLength + schedules.size() + 1;
            Cell cellSplit = row.createCell(col);
            cellSplit.setCellValue(SPLIT);
            cellSplit.setCellStyle(style);

            appendScheduleColnum(errorInfo, schedules, i, style, row, col, DISTANCE);
        }

        sheet0.createFreezePane(1, 2, 1, 2);

        try {
            String exportPath = String.format(Constant.UPLOAD_EXCEL, "competition", fileName + ".xlsx");
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            wb.write(byteArrayOutputStream);
            InputStream inputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
            UploadResult uploadResult = cosCloudBizService.uploadFile(inputStream, exportPath, InsertOnly.OVER_WRITE);
            String resourceUrl = uploadResult.getSourceUrl();
            return resourceUrl;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 拼接日程列
     *
     * @param errorInfo   错误数据
     * @param schedules   赛程
     * @param rowno       行索引
     * @param style       单元格样式
     * @param row         行
     * @param startLength 开始列
     * @param result      类型 result：用时 distance：里程
     */
    private void appendScheduleColnum(List<Map<String, String>> errorInfo, List<CompetitionSchedule> schedules,
                                      int rowno, CellStyle style, Row row, int startLength, String result) {
        for (int j = 0; j < schedules.size(); j++) {
            Cell cellPlayDay = row.createCell(startLength + j + 1);
            CompetitionSchedule schedule = schedules.get(j);
            String formatDate = DateUtils.formatDate(schedule.getPlayDay(), DateUtils.DATE_PATTERN);
            String cellValue = "";
            for (Map.Entry<String, String> entry : errorInfo.get(rowno).entrySet()) {
                if (entry.getKey().contains(result)) {
                    String[] split11 = entry.getKey().split("_");
                    if (split11[1].equals(formatDate)) {
                        cellValue = entry.getValue();
                        break;
                    }
                }
            }
            cellPlayDay.setCellValue(cellValue);
            cellPlayDay.setCellStyle(style);
        }
    }

    /**
     * 表头
     *
     * @param schedules        日程
     * @param type             类型 1：人员 2：团队
     * @param sheet0           sheet0
     * @param marginTitleRowNo 合并表头行号
     * @param titleRowNo       表头行号
     */
    private void createTitleRow(List<CompetitionSchedule> schedules, int type, Sheet sheet0,
                                int marginTitleRowNo, int titleRowNo, Workbook wb) {
        String[] title = cMemberTitles;
        if (type == 2) {
            title = cGroupTitles;
        }
        Row marginTitleRow = sheet0.createRow(marginTitleRowNo);
        Row titleRow = sheet0.createRow(titleRowNo);
        for (int i = 0; i < title.length; i++) {
            Cell cell = titleRow.createCell(i);
            cell.setCellValue(title[i]);
        }
        // 用时
        appendSchedule(schedules, sheet0, marginTitleRowNo, title.length, marginTitleRow, titleRow, "用时（数据格式：00:00:00）", wb);

        // 分割符
        int column = title.length + schedules.size();
        Cell split = titleRow.createCell(column);
        split.setCellValue(SPLIT);

        // 里程
        appendSchedule(schedules, sheet0, marginTitleRowNo, column + 1, marginTitleRow, titleRow, "里程（数据格式：10）", wb);
    }

    /**
     * 拼接日程表头
     *
     * @param schedules        日程
     * @param sheet0           sheet0
     * @param marginTitleRowNo 合并行号
     * @param startLength      日程开始的长度
     * @param marginTitleRow   合并行
     * @param titleRow         表头行
     * @param marginTitle      合并行表头值
     * @param wb
     */
    private void appendSchedule(List<CompetitionSchedule> schedules, Sheet sheet0, int marginTitleRowNo,
                                int startLength, Row marginTitleRow, Row titleRow, String marginTitle, Workbook wb) {
        int cjFirstCol = 0;
        int cjLastCol = 0;
        for (int i = 0; i < schedules.size(); i++) {
            CompetitionSchedule schedule = schedules.get(i);
            String formatDate = DateUtils.formatDate(schedule.getPlayDay(), DateUtils.DATE_PATTERN);
            int col = startLength + i;
            Cell cell = titleRow.createCell(col);
            cell.setCellValue(formatDate);
            if (i == 0) {
                cjFirstCol = col;
            }
            if (i == schedules.size() - 1) {
                cjLastCol = col;
            }
        }
        Cell cc0 = marginTitleRow.createCell(cjFirstCol);
        cc0.setCellValue(marginTitle);
        sheet0.addMergedRegion(new CellRangeAddress(marginTitleRowNo, marginTitleRowNo, (short) cjFirstCol, (short) cjLastCol));

        CellStyle style = wb.createCellStyle();
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//设置前景填充样式
        style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index); // 设置前景色
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
        cc0.setCellStyle(style);
    }

    public int insertPersonResult(String projectId, CompetitionMember competitionMember,
                                  Map<String, String> resultMap, Map<String, String> distanceMap) throws Exception {
        int scheduleSuccess = 0;
        for (Map.Entry<String, String> entry : resultMap.entrySet()) {
            String date = entry.getKey(); // 日期
            String result = entry.getValue(); // 用时
            String distance = distanceMap.get(date); // 里程
            CompetitionSchedule schedule = new CompetitionSchedule();
            schedule.setProjectId(projectId);
            schedule.setPlayDay(DateUtils.parse(date, DateUtils.DATE_PATTERN));
            CompetitionSchedule scheduleDb = competitionScheduleService.getUnique(schedule);

            if (scheduleDb == null) {
                throw new BusinessException(120, "日程不存在");
            }

            if ((StringUtils.isNotEmpty(result) && !result.equals("0.00")) &&
                    StringUtils.isNotEmpty(distance) && !distance.equals("0.00")) {
                String[] split = result.split(":");
                if (split.length != 3) {
                    throw new BusinessException(130, "用时成绩格式不对");
                }
                CompetitionResult query = new CompetitionResult();
                query.setScheduleId(scheduleDb.getId());
                query.setMemberId(competitionMember.getId());
                query.setProjectId(projectId);
                CompetitionResult resultDb = competitionResultService.getUnique(query);
                if (resultDb != null) {
                    competitionResultService.deleteByUnique(query);
                }
                query.setActualRange(distance);
                if (Double.valueOf(distance) >= Double.valueOf(scheduleDb.getDistance())) {
                    query.setIsComplete(YesNoStatus.YES.getCode());
                } else {
                    query.setIsComplete(YesNoStatus.NO.getCode());
                }
                Double total = 0.0;
                String hour = split[0];
                String minute = split[1];
                String second = split[2];
                if (StringUtils.isNotEmpty(hour)) {
                    Double hours = BigDecimalUtils.mul(Double.valueOf(hour), 3600);
                    total = BigDecimalUtils.add(hours, total);
                    query.setHours(hour);
                }
                if (StringUtils.isNotEmpty(minute)) {
                    Double minutes = BigDecimalUtils.mul(Double.valueOf(minute), 60);
                    total = BigDecimalUtils.add(minutes, total);
                    query.setMinutes(minute);
                }
                if (StringUtils.isNotEmpty(second)) {
                    double tempSecond = Double.valueOf(second);
                    Double seconds = Double.valueOf((int) Math.rint(tempSecond));
                    total = BigDecimalUtils.add(seconds, total);
                    query.setSeconds(second);
                }
                query.setSecondsResult(total + "");
                competitionResultService.insert(query);
            }
            scheduleSuccess++;
        }
        return scheduleSuccess;
    }

    public int insertGroupResult(String projectId, String groupId,
                                 Map<String, String> resultMap, Map<String, String> distanceMap) throws Exception {
        int scheduleSuccess = 0;
        for (Map.Entry<String, String> entry : resultMap.entrySet()) {

            String date = entry.getKey(); // 日期
            String result = entry.getValue(); // 用时
            String distance = distanceMap.get(date); // 里程

            CompetitionSchedule schedule = new CompetitionSchedule();
            schedule.setProjectId(projectId);
            schedule.setPlayDay(DateUtils.parse(date, DateUtils.DATE_PATTERN));
            CompetitionSchedule scheduleDb = competitionScheduleService.getUnique(schedule);

            if (scheduleDb == null) {
                throw new BusinessException(120, "日程不存在");
            }

            if ((StringUtils.isNotEmpty(result) && !result.equals("0.00")) &&
                    StringUtils.isNotEmpty(distance) && !distance.equals("0.00")) {
                String[] split = result.split(":");
                if (split.length != 3) {
                    throw new BusinessException(130, "用时成绩格式不对");
                }
                CompetitionGroupResult query = new CompetitionGroupResult();
                query.setScheduleId(scheduleDb.getId());
                query.setGroupId(groupId);
                query.setProjectId(projectId);
                CompetitionGroupResult resultDb = competitionGroupResultService.getUnique(query);
                if (resultDb != null) {
                    competitionGroupResultService.deleteByUnique(query);
                }
                query.setActualRange(distance);
                Double total = 0.0;
                String hour = split[0];
                String minute = split[1];
                String second = split[2];
                if (StringUtils.isNotEmpty(hour)) {
                    Double hours = BigDecimalUtils.mul(Double.valueOf(hour), 3600);
                    total = BigDecimalUtils.add(hours, total);
                    query.setHours(hour);
                }
                if (StringUtils.isNotEmpty(minute)) {
                    Double minutes = BigDecimalUtils.mul(Double.valueOf(minute), 60);
                    total = BigDecimalUtils.add(minutes, total);
                    query.setMinutes(minute);
                }
                if (StringUtils.isNotEmpty(second)) {
                    double tempSecond = Double.valueOf(second);
                    Double seconds = Double.valueOf((int) Math.rint(tempSecond));
                    total = BigDecimalUtils.add(seconds, total);
                    query.setSeconds(second);
                }
                query.setSecondsResult(total + "");
                query.setOpenStatus(YesNoStatus.YES.getCode());
                competitionGroupResultService.insert(query);
            }
            scheduleSuccess++;
        }
        return scheduleSuccess;
    }
}
